package org.dbi.mysql;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

import org.dbi.beans.AlternativeKey;
import org.dbi.beans.Attribute;
import org.dbi.beans.AttributeGroup;
import org.dbi.beans.Database;
import org.dbi.beans.ForeignKey;
import org.dbi.beans.Constraint;
import org.dbi.beans.PrimaryKey;
import org.dbi.beans.Record;
import org.dbi.beans.Reference;

import org.dbi.beans.Key;

import org.dbi.beans.Tabel;
import org.dbi.exceptions.AttribConversionException;
import org.dbi.exceptions.InvalidKeyException;
import org.dbi.exceptions.KeyAlreadyExistsException;
import org.dbi.logger.ErrorLogger;

public class Rdbms {
        private HashMap<String, Database> myDatabases = new HashMap<String, Database>();
        private HashMap<String, Constraint> myConstraints = new HashMap<String, Constraint>();
        private ErrorLogger logger = ErrorLogger.getLogger(getClass().getName());
        private MainDao md;
        private boolean initialised = false;
        private Database currentDatabase;
        private static Rdbms instance = null;
        private referentieleCheck refCheck = null;

        private Rdbms() {
                logger.info("Rdbms Gestart");
                refCheck = new referentieleCheck();

        }

        public static Rdbms getInstance() {
                if (instance == null)
                        instance = new Rdbms();
                return instance;
        }

        public boolean init() {
                if (!initialised) {
                        md = MainDao.getInstance();
                        initialised = true;
                        return initialised;
                } else
                        return initialised;
        }

        public String getRdbmsDatabasesAsString() {
                return myDatabases.keySet().toString();
        }

        public String getTablesAsStringByDatabase() {
                return currentDatabase.getTabellen().keySet().toString();
        }

        public String getAllKeysfromTableAsString(String tabelNaam) {
                return currentDatabase.getTabellen().get(tabelNaam).getConstraint().getKeys().keySet().toString();
        }

        public String getConstraintFromTabel(String tabelNaam) {
                return currentDatabase.getTabellen().get(tabelNaam).getConstraint().getNaam();
        }

        public String getAttributesFromGroupAttrib(String tabelNaam, String keyNaam) {
                return currentDatabase.getTabellen().get(tabelNaam).getConstraint().getKeys().get(keyNaam).getAttributeGroups().keySet().toString();
        }

        public String getRefsFromConstraint(String tabelNaam) {
                return currentDatabase.getTabellen().get(tabelNaam).getConstraint().getRefs().keySet().toString();
        }

        public String getConstraints() {
                return myConstraints.keySet().toString();
        }

        // Alleen in deze package te gebruiken voor de MainDao van belang.
        protected HashMap<String, Database> getRdbmsDatabases() {
                return myDatabases;
        }

        // DDL methoden
        public boolean useDatabase(String dbNaam) {
                boolean useDatabase = false;
                if (myDatabases.containsKey(dbNaam)) {
                        if (currentDatabase != null) {
                                if (currentDatabase.getNaam().equals(dbNaam))
                                        useDatabase = true;
                                else {
                                        myDatabases.remove(currentDatabase.getNaam());
                                        myDatabases.put(currentDatabase.getNaam(), currentDatabase);
                                        currentDatabase = myDatabases.get(dbNaam);
                                        useDatabase = true;
                                }
                        } else {
                                currentDatabase = myDatabases.get(dbNaam);
                                useDatabase = true;
                        }
                } else {
                        useDatabase = false;
                }
                return useDatabase;
        }

        // DDL****************************
        public boolean createDatabase(String dbNaam) {
                if (!myDatabases.containsKey(dbNaam)) {
                        Database db = new Database(dbNaam);
                        myDatabases.put(dbNaam, db);
                        return true;
                } else
                        return false;
        }

        public boolean createTable(String constraintNaam, String tabelNaam, String databaseNaam) {

                if (!myConstraints.containsKey(constraintNaam)) {
                        if (myDatabases.containsKey(databaseNaam)) {
                                if (!myDatabases.get(databaseNaam).getTabellen().containsKey(tabelNaam)) {
                                        Constraint cons = new Constraint(constraintNaam);
                                        myConstraints.put(constraintNaam, cons);
                                        myDatabases.get(databaseNaam).addSingleTabel(new Tabel(tabelNaam, cons));
                                        return true;
                                } else {
                                        logger.info("Tabel " + tabelNaam + " bestaat al in database: " + databaseNaam);
                                        return false;
                                }
                        } else {
                                logger.info("Database bestaat niet: " + databaseNaam);
                                return false;
                        }
                } else {
                        logger.info("Constraint bestaat al: " + constraintNaam);
                        return false;
                }
        }

        public boolean createAttribute(String attribNaam, String databaseNaam, String tabelNaam, String columnType, boolean mandatory) {
                if (myDatabases.containsKey(databaseNaam)) {
                        if (myDatabases.get(databaseNaam).getTabellen().containsKey(tabelNaam)) {
                                if (!myDatabases.get(databaseNaam).getTabellen().get(tabelNaam).getAttributes().containsKey(attribNaam)) {
                                        myDatabases.get(databaseNaam).getTabellen().get(tabelNaam).addSingleAttribute(new Attribute(attribNaam, columnType, mandatory));
                                        return true;
                                } else {
                                        logger.info("Attribute " + attribNaam + " bestaat al in tabel " + tabelNaam + " in database " + databaseNaam);
                                        return false;
                                }

                        } else {
                                logger.info("Tabel bestaat niet: " + tabelNaam);
                                return false;
                        }
                } else {
                        logger.info("Database bestaat niet: " + databaseNaam);
                        return false;
                }
        }

        public boolean createConstraint(String consName, String keyOrRefName, String AttribGroupName, ArrayList<Object[]> attributen) {
                Key myKey = null;
                boolean returnValue = false;

                AttributeGroup ag = new AttributeGroup(AttribGroupName);
                try {
                        if (attributen == null)
                                throw new NullPointerException("Geen attributen gevonden");
                        else {

                                for (Object[] a : attributen)
                                        ag.addSingleAttribute(createAttributeFromObject(a));

                                int tblCounter = 0;
                                int tblTotalSize = 0;

                                for (Database db : myDatabases.values()) {
                                        tblTotalSize += db.getTabellen().size();
                                        for (Tabel tbl : db.getTabellen().values()) {
                                                if (tbl.getConstraint().getNaam().equals(consName)) {
                                                        if (!tbl.getConstraint().getKeys().containsKey(keyOrRefName)) {
                                                                if (keyOrRefName.startsWith("AK")) {
                                                                        myKey = new AlternativeKey(keyOrRefName);
                                                                        myKey.addSingleAttributeGroup(ag);
                                                                        tbl.getConstraint().addSingleKeys(myKey);
                                                                } else if (keyOrRefName.startsWith("FK")) {
                                                                        myKey = new ForeignKey(keyOrRefName);
                                                                        myKey.addSingleAttributeGroup(ag);
                                                                        tbl.getConstraint().addSingleKeys(myKey);
                                                                } else if (keyOrRefName.startsWith("PK")) {
                                                                        myKey = new PrimaryKey(keyOrRefName);
                                                                        myKey.addSingleAttributeGroup(ag);
                                                                        tbl.getConstraint().addSingleKeys(myKey);
                                                                } else if (keyOrRefName.startsWith("REF")) {
                                                                        Reference ref = new Reference(keyOrRefName, null, null);
                                                                        tbl.getConstraint().addsingleReference(ref);
                                                                } else
                                                                        throw new InvalidKeyException(keyOrRefName);
                                                        } else {
                                                                tbl.getConstraint().getKey(keyOrRefName).addSingleAttributeGroup(ag);
                                                        }

                                                } else {
                                                        tblCounter++;
                                                }

                                        }
                                }
                                if (tblCounter == tblTotalSize) {
                                        logger.info(tblCounter + " - " + tblTotalSize + "Constraint is niet toegevoegd");
                                        returnValue = false;
                                } else {
                                        logger.info(tblCounter + " - " + tblTotalSize + " - " + consName + " constraint is toegevoegd of aangepast.");
                                        returnValue = true;
                                }
                        }
                } catch (NullPointerException npe) {
                        logger.info(npe.getMessage());
                        returnValue = false;
                } catch (InvalidKeyException e) {
                        logger.info(e.getMessage());
                }

                return returnValue;

        }

        // before UseDatabase call
        public boolean createReference(String refNaam, String fromGrp, String toGrp) {
                
                AttributeGroup fromGroup = null;
                AttributeGroup toGroup = null;
                Tabel tabelToAdd = null;

                for (Database database : myDatabases.values()) {
                        for (Tabel tabel : database.getTabellen().values()) {
                                for (Key key : tabel.getConstraint().getKeys().values()) {
                                        for (AttributeGroup attribGroup : key.getAttributeGroups().values()) {
                                                if (attribGroup.getNaam().equals(fromGrp)) {
                                                        fromGroup = attribGroup;
                                                        tabelToAdd = tabel;
                                                }
                                
                                                if (attribGroup.getNaam().equals(toGrp)) {
                                                        toGroup = attribGroup;

                                                }
                                                
                                        }
                                }
                        }
                }
                
                // check of de groups wel bestaan
                if (fromGroup != null && toGroup != null) {
                        // check of er evenveel attribs zijn

                        ArrayList<Attribute> fromAttribs = new ArrayList<Attribute>();
                        ArrayList<Attribute> toAttribs = new ArrayList<Attribute>();

                        fromAttribs.addAll(fromGroup.getAttributes().values());
                        toAttribs.addAll(toGroup.getAttributes().values());

                        if (fromGroup.getAttributes().size() == toGroup.getAttributes().size()) {
                                // check of de kolomtypes gelijk zijn
                                for (int aantalAttribs = 0; aantalAttribs < fromGroup.getAttributes().size(); aantalAttribs++) {
                                        logger.info("Vergelijk: " + fromAttribs.get(aantalAttribs).getNaam() + " en " + toAttribs.get(aantalAttribs).getNaam());
                                        if (!tabelToAdd.compare(fromAttribs.get(aantalAttribs).getColumnType(), toAttribs.get(aantalAttribs).getColumnType())) {
                                                logger.info("kolomTypen zijn niet gelijk aan elkaar.");
                                                return false;
                                        }
                                }

                                return currentDatabase.getTabellen().get(tabelToAdd.getNaam()).getConstraint().addsingleReference(new Reference(refNaam, fromGroup, toGroup));

                        } else {
                                logger.info("Niet evenveel attribute in de group, kan de reference niet leggen.");
                                return false;
                        }
                } else {
                        if (fromGroup == null)
                                logger.info("Een group bestaat niet: fromGroup ");
                        if (toGroup == null)
                                logger.info("Een group bestaat niet: toGroup: ");
                        return false;
                }

        }

        private boolean insertRecord(String tabelNaam, String[] columns, Object[] values) {
                Tabel tbl = currentDatabase.getTabellen().get(tabelNaam);

                if (tbl != null) {
                        return refCheck.refCheckInsertRecord(currentDatabase, getAttributesFromTabel(tbl), referentieleCheck(tbl), columns, values);

                } else {
                        logger.info("Tabel " + tabelNaam + " bestaat niet in database " + currentDatabase);
                        return false;
                }

        }

        public boolean dropDatabase(String databaseNaam) {
                if (myDatabases.get(databaseNaam) != null) {
                        if (!currentDatabase.getNaam().equals(databaseNaam)) {
                                myDatabases.remove(databaseNaam);
                                logger.info("Database is verwijderd");
                                return true;
                        } else {
                                logger.info("Database is in gebruik");
                                return false;
                        }
                } else {
                        logger.info("Database bestaat niet");
                        return false;
                }
        }

        private boolean dropCheckTable(String tabelNaam) {
                Tabel tbl = currentDatabase.getTabellen().get(tabelNaam);
                
                if (tbl != null) {
                        return refCheck.refCheckDropTable(currentDatabase, getAttributesFromTabel(tbl), referentieleCheck(tbl));
                } else {
                        logger.info("table bestaat niet " + tabelNaam);
                        return false;
                }
        }

        public boolean dropTable(String tabelNaam) {
                if (dropCheckTable(tabelNaam)) {
                        return currentDatabase.dropTabel(tabelNaam);
                } else {
                        logger.info("Tabel " + tabelNaam + " mag niet verwijderd worden");
                        return false;
                }
        }

        private boolean dropRecordCheck(String tabelNaam, String[] whereColumns, Object[] whereValues) {
                Tabel tbl = currentDatabase.getTabellen().get(tabelNaam);
                if (tbl != null) {
                        return refCheck.refCheckDeleteRecords(tbl, currentDatabase, getAttributesFromTabel(tbl), referentieleCheck(tbl), whereColumns, whereValues);
                } else {
                        logger.info("Tabel " + tabelNaam + " bestaat niet!");
                        return false;
                }

        }

        public boolean deleteRecord(String tabelNaam, String[] whereColumns, Object[] whereValues) {
                if (dropRecordCheck(tabelNaam, whereColumns, whereValues)) {
                        return true;
                } else {
                        return false;
                }
        }

        // EIND DDL****************************

        private ArrayList<AttributeGroup> getAttributesFromTabel(Tabel currentTabel) {
                ArrayList<AttributeGroup> attribGrpFromCurrentTabel = new ArrayList<AttributeGroup>();
                for (Key key : currentTabel.getConstraint().getKeys().values()) {
                        attribGrpFromCurrentTabel.addAll(key.getAttributeGroups().values());
                }
                return attribGrpFromCurrentTabel;
        }

        private ArrayList<Reference> referentieleCheck(Tabel currentTabel) {
                // public ArrayList<Reference> referentieleCheck() {

                // Tabel currentTabel = currentDatabase.getTabellen().get("tblTabel");
                Database database = null;
                Constraint cons = currentTabel.getConstraint();
                ArrayList<AttributeGroup> attribGrpFromCurrentTabel = getAttributesFromTabel(currentTabel);
                ArrayList<Reference> refsContainsAGroup = new ArrayList<Reference>();

                // Kijk welke db bij de tabel hoort.
                for (Database db : myDatabases.values()) {
                        if (db.getTabellen().get(currentTabel.getNaam()) != null) {
                                database = db;
                        }
                }

                // Als deze voorkomen in 1 van de references dan moeten we hier na
                // kijken

                for (Tabel tbl : database.getTabellen().values()) {

                        for (Reference ref : tbl.getConstraint().getRefs().values()) {
                                if (attribGrpFromCurrentTabel.contains(ref.getToGroup())) {
                                        refsContainsAGroup.add(ref);
                                }
                                if (attribGrpFromCurrentTabel.contains(ref.getFromGroup())) {
                                        refsContainsAGroup.add(ref);
                                }
                        }
                }

                return refsContainsAGroup;
        }

        public String getCurrentDatabase() {
                return currentDatabase.getNaam();
        }

        // DML methoden

        // select * from tabelnaam
        public ArrayList<Object[]> select(String tblNaam) {
                if (currentDatabase.getTabellen().containsKey(tblNaam))
                        return currentDatabase.getTabellen().get(tblNaam).selectAllRecords();
                else
                        return null;
        }

        // select naam naam from tabel
        public ArrayList<Object[]> select(String tblNaam, String[] columnNames) {
                if (currentDatabase.getTabellen().containsKey(tblNaam))
                        return currentDatabase.getTabellen().get(tblNaam).selectSpecificColumn(columnNames);
                else
                        return null;
        }

        // select naam naam from tabel where naam = value
        public ArrayList<Object[]> select(String tblNaam, String[] columnNames, String[] whereColumnName, Object[] whereValue) {
                if (currentDatabase.getTabellen().containsKey(tblNaam))
                        return currentDatabase.getTabellen().get(tblNaam).selectSpecificColumnWithWhere(columnNames, whereColumnName, whereValue);
                else
                        return null;
        }

        // de insert werkt op de manier van sql
        // insert into table en de values zijn de waarden uit de record
        public boolean insert(String tblNaam, String[] columns, Object[] values) {
                if (insertRecord(tblNaam, columns, values)) {
                        logger.info("Ik mag door:)");
                        if (currentDatabase.getTabellen().containsKey(tblNaam)) {
                                return currentDatabase.getTabellen().get(tblNaam).addRecord(columns, values);
                        } else {
                                logger.info("Tabel bestaat niet");
                                return false;
                        }
                } else {
                        logger.info("volgens de refCheck mag je niet door");
                        return false;
                }

        }

        public boolean update(String tblNaam, String[] columnsToEdit, Object[] newValues, String[] whereColumnNames, Object[] whereValues) {
                if (refCheckUpdateRecord(tblNaam, columnsToEdit, newValues, whereColumnNames, whereValues))
                        if (currentDatabase.getTabellen().containsKey(tblNaam)) {
                                return currentDatabase.getTabellen().get(tblNaam).updateRecord(columnsToEdit, newValues, whereColumnNames, whereValues);

                        } else
                                return false;
                else
                        return false;

        }

        private Attribute createAttributeFromObject(Object[] attributesValues) {
                Attribute a = null;
                try {
                        if (attributesValues.length == 3) {
                                String AttribNaam = attributesValues[0].toString();
                                String AttribColumnType = attributesValues[1].toString();
                                boolean AttribMandatory = new Boolean(attributesValues[2].toString());
                                a = new Attribute(AttribNaam, AttribColumnType, AttribMandatory);
                        } else if (attributesValues.length < 3) {
                                throw new AttribConversionException("Te weinig waarden op gegeven");
                        } else if (attributesValues.length > 3) {
                                throw new AttribConversionException("Te veel waarden op gegeven");
                        }
                } catch (AttribConversionException e) {
                        logger.severe(e.getMessage());
                }
                return a;
        }

        private boolean refCheckUpdateRecord(String tblNaam, String[] columnsToEdit, Object[] newValues, String[] whereColumnNames, Object[] whereValues) {
                Tabel tbl = currentDatabase.getTabellen().get(tblNaam);
                PrimaryKey pk = null;
                ForeignKey fk = null;
                for (Constraint c : myConstraints.values()) {
                        for (Key k : c.getKeys().values()) {
                                for (AttributeGroup ag : k.getAttributeGroups().values()) {
                                        for (AttributeGroup agg : getAttributesFromTabel(tbl)) {
                                                
                                                if (ag.getNaam().equals(agg.getNaam())) {

                                                        if (k instanceof PrimaryKey)
                                                                pk = (PrimaryKey) k;
                                                        else if (k instanceof ForeignKey)
                                                                fk = (ForeignKey) k;
                                                }
                                        }
                                }
                        }
                }

                if (tbl != null && pk != null) {
                

                        return refCheck.refCheckUpdateRecord(pk, fk, referentieleCheck(tbl), columnsToEdit);

                } else {
                        logger.info("Tabel " + tblNaam + " bestaat niet in database " + currentDatabase);
                        return false;
                }

        }
}